{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据预览"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import sys \n",
    "from sklearn.model_selection import KFold\n",
    "%matplotlib inline\n",
    "# %matplotlib notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>target</th>\n",
       "      <th>ps_ind_01</th>\n",
       "      <th>ps_ind_02_cat</th>\n",
       "      <th>ps_ind_03</th>\n",
       "      <th>ps_ind_04_cat</th>\n",
       "      <th>ps_ind_05_cat</th>\n",
       "      <th>ps_ind_06_bin</th>\n",
       "      <th>ps_ind_07_bin</th>\n",
       "      <th>ps_ind_08_bin</th>\n",
       "      <th>...</th>\n",
       "      <th>ps_calc_11</th>\n",
       "      <th>ps_calc_12</th>\n",
       "      <th>ps_calc_13</th>\n",
       "      <th>ps_calc_14</th>\n",
       "      <th>ps_calc_15_bin</th>\n",
       "      <th>ps_calc_16_bin</th>\n",
       "      <th>ps_calc_17_bin</th>\n",
       "      <th>ps_calc_18_bin</th>\n",
       "      <th>ps_calc_19_bin</th>\n",
       "      <th>ps_calc_20_bin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  target  ps_ind_01  ps_ind_02_cat  ps_ind_03  ps_ind_04_cat  \\\n",
       "0   7       0          2              2          5              1   \n",
       "1   9       0          1              1          7              0   \n",
       "2  13       0          5              4          9              1   \n",
       "3  16       0          0              1          2              0   \n",
       "4  17       0          0              2          0              1   \n",
       "\n",
       "   ps_ind_05_cat  ps_ind_06_bin  ps_ind_07_bin  ps_ind_08_bin       ...        \\\n",
       "0              0              0              1              0       ...         \n",
       "1              0              0              0              1       ...         \n",
       "2              0              0              0              1       ...         \n",
       "3              0              1              0              0       ...         \n",
       "4              0              1              0              0       ...         \n",
       "\n",
       "   ps_calc_11  ps_calc_12  ps_calc_13  ps_calc_14  ps_calc_15_bin  \\\n",
       "0           9           1           5           8               0   \n",
       "1           3           1           1           9               0   \n",
       "2           4           2           7           7               0   \n",
       "3           2           2           4           9               0   \n",
       "4           3           1           1           3               0   \n",
       "\n",
       "   ps_calc_16_bin  ps_calc_17_bin  ps_calc_18_bin  ps_calc_19_bin  \\\n",
       "0               1               1               0               0   \n",
       "1               1               1               0               1   \n",
       "2               1               1               0               1   \n",
       "3               0               0               0               0   \n",
       "4               0               0               1               1   \n",
       "\n",
       "   ps_calc_20_bin  \n",
       "0               1  \n",
       "1               0  \n",
       "2               0  \n",
       "3               0  \n",
       "4               0  \n",
       "\n",
       "[5 rows x 59 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    ".\\# 解决matplotlib显示中文问题\n",
    "# 仅适用于Windows\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 指定默认字体\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题\n",
    "test=pd.read_csv(r'.\\\\test.csv')\n",
    "train=pd.read_csv(r'.\\\\train.csv')\n",
    "sample=pd.read_csv(r'.\\\\sample_submission.csv')\n",
    "test_id=test['id']\n",
    "combine=[train,test]\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 595212 entries, 0 to 595211\n",
      "Data columns (total 59 columns):\n",
      "id                595212 non-null int64\n",
      "target            595212 non-null int64\n",
      "ps_ind_01         595212 non-null int64\n",
      "ps_ind_02_cat     595212 non-null int64\n",
      "ps_ind_03         595212 non-null int64\n",
      "ps_ind_04_cat     595212 non-null int64\n",
      "ps_ind_05_cat     595212 non-null int64\n",
      "ps_ind_06_bin     595212 non-null int64\n",
      "ps_ind_07_bin     595212 non-null int64\n",
      "ps_ind_08_bin     595212 non-null int64\n",
      "ps_ind_09_bin     595212 non-null int64\n",
      "ps_ind_10_bin     595212 non-null int64\n",
      "ps_ind_11_bin     595212 non-null int64\n",
      "ps_ind_12_bin     595212 non-null int64\n",
      "ps_ind_13_bin     595212 non-null int64\n",
      "ps_ind_14         595212 non-null int64\n",
      "ps_ind_15         595212 non-null int64\n",
      "ps_ind_16_bin     595212 non-null int64\n",
      "ps_ind_17_bin     595212 non-null int64\n",
      "ps_ind_18_bin     595212 non-null int64\n",
      "ps_reg_01         595212 non-null float64\n",
      "ps_reg_02         595212 non-null float64\n",
      "ps_reg_03         595212 non-null float64\n",
      "ps_car_01_cat     595212 non-null int64\n",
      "ps_car_02_cat     595212 non-null int64\n",
      "ps_car_03_cat     595212 non-null int64\n",
      "ps_car_04_cat     595212 non-null int64\n",
      "ps_car_05_cat     595212 non-null int64\n",
      "ps_car_06_cat     595212 non-null int64\n",
      "ps_car_07_cat     595212 non-null int64\n",
      "ps_car_08_cat     595212 non-null int64\n",
      "ps_car_09_cat     595212 non-null int64\n",
      "ps_car_10_cat     595212 non-null int64\n",
      "ps_car_11_cat     595212 non-null int64\n",
      "ps_car_11         595212 non-null int64\n",
      "ps_car_12         595212 non-null float64\n",
      "ps_car_13         595212 non-null float64\n",
      "ps_car_14         595212 non-null float64\n",
      "ps_car_15         595212 non-null float64\n",
      "ps_calc_01        595212 non-null float64\n",
      "ps_calc_02        595212 non-null float64\n",
      "ps_calc_03        595212 non-null float64\n",
      "ps_calc_04        595212 non-null int64\n",
      "ps_calc_05        595212 non-null int64\n",
      "ps_calc_06        595212 non-null int64\n",
      "ps_calc_07        595212 non-null int64\n",
      "ps_calc_08        595212 non-null int64\n",
      "ps_calc_09        595212 non-null int64\n",
      "ps_calc_10        595212 non-null int64\n",
      "ps_calc_11        595212 non-null int64\n",
      "ps_calc_12        595212 non-null int64\n",
      "ps_calc_13        595212 non-null int64\n",
      "ps_calc_14        595212 non-null int64\n",
      "ps_calc_15_bin    595212 non-null int64\n",
      "ps_calc_16_bin    595212 non-null int64\n",
      "ps_calc_17_bin    595212 non-null int64\n",
      "ps_calc_18_bin    595212 non-null int64\n",
      "ps_calc_19_bin    595212 non-null int64\n",
      "ps_calc_20_bin    595212 non-null int64\n",
      "dtypes: float64(10), int64(49)\n",
      "memory usage: 267.9 MB\n"
     ]
    }
   ],
   "source": [
    "train.info()\n",
    "train.describe()\n",
    "ps_car_09_cat_train=train['ps_car_09_cat']\n",
    "ps_car_09_cat_test=test['ps_car_09_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_calc=train.columns[train.columns.str[6]=='c']\n",
    "train.drop(col_calc,axis=1,inplace=True)\n",
    "test.drop(col_calc,axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corr=train.corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 每列数据的集合和个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for data in combine:\n",
    "    data.drop(['id'],axis=1,inplace=True)\n",
    "# unique=pd.DataFrame(index=np.arange(0,70482,1),columns=col.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ps_ind_01            8\n",
      "ps_ind_02_cat        5\n",
      "ps_ind_03           12\n",
      "ps_ind_04_cat        3\n",
      "ps_ind_05_cat        8\n",
      "ps_ind_06_bin        2\n",
      "ps_ind_07_bin        2\n",
      "ps_ind_08_bin        2\n",
      "ps_ind_09_bin        2\n",
      "ps_ind_10_bin        2\n",
      "ps_ind_11_bin        2\n",
      "ps_ind_12_bin        2\n",
      "ps_ind_13_bin        2\n",
      "ps_ind_14            5\n",
      "ps_ind_15           14\n",
      "ps_ind_16_bin        2\n",
      "ps_ind_17_bin        2\n",
      "ps_ind_18_bin        2\n",
      "ps_reg_01           10\n",
      "ps_reg_02           19\n",
      "ps_reg_03         5013\n",
      "ps_car_01_cat       13\n",
      "ps_car_02_cat        3\n",
      "ps_car_03_cat        3\n",
      "ps_car_04_cat       10\n",
      "ps_car_05_cat        3\n",
      "ps_car_06_cat       18\n",
      "ps_car_07_cat        3\n",
      "ps_car_08_cat        2\n",
      "ps_car_09_cat        6\n",
      "ps_car_10_cat        3\n",
      "ps_car_11_cat      104\n",
      "ps_car_11            5\n",
      "ps_car_12          184\n",
      "ps_car_13        70482\n",
      "ps_car_14          850\n",
      "ps_car_15           15\n",
      "Name: Count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "count=pd.DataFrame(columns=test.columns.values,index=['Count'])\n",
    "for data in test.columns.values:\n",
    "    count[data]=len(set(train[data].values))\n",
    "#     unique[data]=pd.Series(list(set(train[data].values)))\n",
    "# unique_count=pd.concat([count,unique])\n",
    "print(count.T['Count'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 有缺失值的列和缺失比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for data in combine:\n",
    "#     data.drop(['ps_reg_03','ps_car_05_cat','ps_car_14','ps_car_07_cat'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train[train==-1]=np.nan\n",
    "train_null=train.isnull().any()\n",
    "# train_null[train_null==True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test[test==-1]=np.nan\n",
    "test_null=test.isnull().any()\n",
    "# test_null[test_null==True]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ps_ind_02_cat</th>\n",
       "      <th>ps_ind_04_cat</th>\n",
       "      <th>ps_ind_05_cat</th>\n",
       "      <th>ps_reg_03</th>\n",
       "      <th>ps_car_01_cat</th>\n",
       "      <th>ps_car_02_cat</th>\n",
       "      <th>ps_car_03_cat</th>\n",
       "      <th>ps_car_05_cat</th>\n",
       "      <th>ps_car_07_cat</th>\n",
       "      <th>ps_car_09_cat</th>\n",
       "      <th>ps_car_11</th>\n",
       "      <th>ps_car_12</th>\n",
       "      <th>ps_car_14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Train_Scale</th>\n",
       "      <td>0.000363</td>\n",
       "      <td>0.000139</td>\n",
       "      <td>0.00976</td>\n",
       "      <td>0.181065</td>\n",
       "      <td>0.00018</td>\n",
       "      <td>0.000008</td>\n",
       "      <td>0.690898</td>\n",
       "      <td>0.447825</td>\n",
       "      <td>0.019302</td>\n",
       "      <td>0.000956</td>\n",
       "      <td>0.000008</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>0.071605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ps_ind_02_cat  ps_ind_04_cat  ps_ind_05_cat  ps_reg_03  \\\n",
       "Train_Scale       0.000363       0.000139        0.00976   0.181065   \n",
       "\n",
       "             ps_car_01_cat  ps_car_02_cat  ps_car_03_cat  ps_car_05_cat  \\\n",
       "Train_Scale        0.00018       0.000008       0.690898       0.447825   \n",
       "\n",
       "             ps_car_07_cat  ps_car_09_cat  ps_car_11  ps_car_12  ps_car_14  \n",
       "Train_Scale       0.019302       0.000956   0.000008   0.000002   0.071605  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_scale=pd.DataFrame(index=['Train_Scale'],columns=train_null[train_null==True].index)\n",
    "for index in train_null[train_null==True].index:\n",
    "    col_nu=train[index].isnull()\n",
    "    train_scale[index]=len(train[index][col_nu==True])/len(train[index])\n",
    "train_scale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ps_ind_02_cat</th>\n",
       "      <th>ps_ind_04_cat</th>\n",
       "      <th>ps_ind_05_cat</th>\n",
       "      <th>ps_reg_03</th>\n",
       "      <th>ps_car_01_cat</th>\n",
       "      <th>ps_car_02_cat</th>\n",
       "      <th>ps_car_03_cat</th>\n",
       "      <th>ps_car_05_cat</th>\n",
       "      <th>ps_car_07_cat</th>\n",
       "      <th>ps_car_09_cat</th>\n",
       "      <th>ps_car_11</th>\n",
       "      <th>ps_car_14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Test_Scale</th>\n",
       "      <td>0.000344</td>\n",
       "      <td>0.000162</td>\n",
       "      <td>0.009756</td>\n",
       "      <td>0.181094</td>\n",
       "      <td>0.000179</td>\n",
       "      <td>0.000006</td>\n",
       "      <td>0.690972</td>\n",
       "      <td>0.448423</td>\n",
       "      <td>0.019412</td>\n",
       "      <td>0.000982</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>0.071465</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ps_ind_02_cat  ps_ind_04_cat  ps_ind_05_cat  ps_reg_03  \\\n",
       "Test_Scale       0.000344       0.000162       0.009756   0.181094   \n",
       "\n",
       "            ps_car_01_cat  ps_car_02_cat  ps_car_03_cat  ps_car_05_cat  \\\n",
       "Test_Scale       0.000179       0.000006       0.690972       0.448423   \n",
       "\n",
       "            ps_car_07_cat  ps_car_09_cat  ps_car_11  ps_car_14  \n",
       "Test_Scale       0.019412       0.000982   0.000001   0.071465  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_scale=pd.DataFrame(index=['Test_Scale'],columns=test_null[test_null==True].index)\n",
    "for index in test_null[test_null==True].index:\n",
    "    col_nu=test[index].isnull()\n",
    "    test_scale[index]=len(test[index][col_nu==True])/len(test[index])\n",
    "test_scale"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填充ps_car_09_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01       -0.102582\n",
       "ps_car_01_cat    0.272900\n",
       "ps_car_03_cat    0.272407\n",
       "ps_car_05_cat   -0.327751\n",
       "ps_car_09_cat    1.000000\n",
       "ps_car_11        0.118969\n",
       "Name: ps_car_09_cat, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_09_cat'])>0.1]['ps_car_09_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_car09=[]\n",
    "len_guess=np.zeros((12))\n",
    "for data in combine:\n",
    "    for i in range(0,12):\n",
    "        guess=data[data['ps_car_01_cat']==i]['ps_car_09_cat'].dropna()\n",
    "        for j in range(0,5):                   #先根据数字和次数存储起来，找到列中出现最多次数的那个数字\n",
    "            len_guess[j]=len(guess[guess==j])\n",
    "        guess_car09.append(len_guess.argmax())\n",
    "    for i in range(0,12):\n",
    "        data.loc[(data['ps_car_09_cat'].isnull())&(data['ps_car_01_cat']==i),'ps_car_09_cat']=guess_car09[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train['ps_car_09_cat'].fillna(2,inplace=True)\n",
    "train['ps_car_09_cat'] = train['ps_car_09_cat'].astype(int)\n",
    "test['ps_car_09_cat'].fillna(2,inplace=True)\n",
    "test['ps_car_09_cat'] = test['ps_car_09_cat'].astype(int)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_car_03_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_car_03_cat    1.000000\n",
       "ps_car_05_cat    0.489789\n",
       "ps_car_08_cat   -0.248662\n",
       "ps_car_09_cat    0.272407\n",
       "Name: ps_car_03_cat, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_03_cat'])>0.2]['ps_car_03_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_ps03 = np.zeros((2,5))\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        for j in range(0,5):\n",
    "            guess=data[(data['ps_car_08_cat']==i)&(data['ps_car_09_cat']==j)]['ps_car_03_cat'].dropna()\n",
    "            if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "                ps_car_03_cat_guess=0\n",
    "            else:\n",
    "                ps_car_03_cat_guess=1\n",
    "            guess_ps03[i][j]=ps_car_03_cat_guess\n",
    "            \n",
    "    for i in range(0,2):\n",
    "        for j in range(0,5):\n",
    "            data.loc[(data['ps_car_03_cat'].isnull())&(data['ps_car_08_cat']==i)&(data['ps_car_09_cat']==j),'ps_car_03_cat']=guess_ps03[i][j]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_car_05_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01        0.213009\n",
       "ps_car_03_cat    0.489789\n",
       "ps_car_05_cat    1.000000\n",
       "ps_car_09_cat   -0.327751\n",
       "Name: ps_car_05_cat, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_05_cat'])>0.2]['ps_car_05_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_ps05 = np.zeros((8,5))\n",
    "for data in combine:\n",
    "    for i in range(0,8):\n",
    "        for j in range(0,5):\n",
    "            guess=data[(data['ps_ind_01']==i)&(data['ps_car_09_cat']==j)]['ps_car_05_cat'].dropna()\n",
    "            if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "                ps_car_05_cat_guess=0\n",
    "            else:\n",
    "                ps_car_05_cat_guess=1\n",
    "            guess_ps05[i][j]=ps_car_05_cat_guess\n",
    "            \n",
    "    for i in range(0,8):\n",
    "        for j in range(0,5):\n",
    "            data.loc[(data['ps_car_05_cat'].isnull())&(data['ps_ind_01']==i)&(data['ps_car_09_cat']==j),'ps_car_05_cat']=guess_ps05[i][j]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充['ps_ind_04_cat']列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_02_cat    0.147110\n",
       "ps_ind_04_cat    1.000000\n",
       "ps_ind_06_bin   -0.297737\n",
       "ps_ind_07_bin    0.304949\n",
       "Name: ps_ind_04_cat, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_ind_04_cat'])>0.1]['ps_ind_04_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_pic = np.zeros((2,2))\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            guess=data[(data['ps_ind_06_bin']==i)&(data['ps_ind_07_bin']==j)]['ps_ind_04_cat'].dropna()\n",
    "            if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "                ps_ind_04_cat_guess=0\n",
    "            else:\n",
    "                ps_ind_04_cat_guess=1\n",
    "            guess_pic[i][j]=ps_ind_04_cat_guess\n",
    "            \n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            data.loc[(data['ps_ind_04_cat'].isnull())&(data['ps_ind_06_bin']==i)&(data['ps_ind_07_bin']==j),'ps_ind_04_cat']=guess_pic[i][j]\n",
    "    data['ps_ind_04_cat'] = data['ps_ind_04_cat'].astype(int)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充\tps_ind_02_cat\t列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_02_cat    1.000000\n",
       "ps_ind_04_cat    0.147110\n",
       "ps_ind_06_bin   -0.125653\n",
       "ps_ind_09_bin    0.110076\n",
       "ps_car_12       -0.110247\n",
       "Name: ps_ind_02_cat, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_ind_02_cat'])>0.1]['ps_ind_02_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 0, 1, ..., 0, 1, 0])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['ps_ind_04_cat'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_pi02 = np.zeros((2,2))\n",
    "len_guess=np.zeros((4))\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            guess=data[(data['ps_ind_04_cat']==i)&(data['ps_ind_06_bin']==j)]['ps_ind_02_cat'].dropna()\n",
    "            for n in range(0,4):                   #先根据数字和次数存储起来，找到列中出现最多次数的那个数字\n",
    "                len_guess[n]=len(guess[guess==n])\n",
    "            guess_pi02[i][j]=len_guess.argmax()+1\n",
    "            \n",
    "    for i in range(0,2):\n",
    "        for j in range(0,2):\n",
    "            data.loc[(data['ps_ind_02_cat'].isnull())&(data['ps_ind_04_cat']==i)&(data['ps_ind_06_bin']==j),'ps_ind_02_cat']=guess_pi02[i][j]\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_ind_05_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_03        0.053250\n",
       "ps_ind_05_cat    1.000000\n",
       "ps_car_07_cat    0.053777\n",
       "Name: ps_ind_05_cat, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_ind_05_cat'])>0.05]['ps_ind_05_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_pi=[]\n",
    "len_guess=np.zeros((7))\n",
    "for data in combine:\n",
    "    for i in range(0,12):\n",
    "        guess=data[data['ps_ind_03']==i]['ps_ind_05_cat'].dropna()\n",
    "        for j in range(0,7):                   #先根据数字和次数存储起来，找到列中出现最多次数的那个数字\n",
    "            len_guess[j]=len(guess[guess==j])\n",
    "        guess_pi.append(len_guess.argmax())\n",
    "    for i in range(0,12):\n",
    "        data.loc[(data['ps_ind_05_cat'].isnull())&(data['ps_ind_03']==i),'ps_ind_05_cat']=guess_pi[i]\n",
    "    data['ps_ind_05_cat'] = data['ps_ind_05_cat'].astype(int)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_reg_03\t列  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01        0.139125\n",
       "ps_ind_09_bin    0.133761\n",
       "ps_reg_01        0.637035\n",
       "ps_reg_02        0.516457\n",
       "ps_reg_03        1.000000\n",
       "ps_car_01_cat    0.230339\n",
       "ps_car_02_cat   -0.135690\n",
       "Name: ps_reg_03, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_reg_03'])>0.1]['ps_reg_03']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ps_reg_01和ps_reg_02，这里只填充了一部分数据，还有的交叉数据是全部为nan\n",
    "guess_ps_reg=np.zeros((10,19))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,19,1):\n",
    "            guess=data[(data['ps_reg_01']==i/10)&(data['ps_reg_02']==j/10)]['ps_reg_03'].dropna()\n",
    "            guess_ps_reg[i][j]=guess.median()#median()\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,19,1):\n",
    "            data.loc[(data['ps_reg_01']==i/10)&(data['ps_reg_02']==j/10)&(data['ps_reg_03'].isnull()),'ps_reg_03']=guess_ps_reg[i][j]\n",
    "    data['ps_reg_03'] = data['ps_reg_03'].astype(float)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ps_reg_01和ps_car_01_cat 填充剩下的一部分缺失值\n",
    "guess_ps_reg=np.zeros((10,12))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,12,1):\n",
    "            guess=data[(data['ps_reg_01']==i/10)&(data['ps_car_01_cat']==j)]['ps_reg_03'].dropna()\n",
    "            guess_ps_reg[i][j]=guess.median()#median()\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,12,1):\n",
    "            data.loc[(data['ps_reg_01']==i/10)&(data['ps_car_01_cat']==j)&(data['ps_reg_03'].isnull()),'ps_reg_03']=guess_ps_reg[i][j]\n",
    "    data['ps_reg_03'] = data['ps_reg_03'].astype(float)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ps_reg_03_dur</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(0.0573, 0.857]</td>\n",
       "      <td>0.031785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(0.857, 1.652]</td>\n",
       "      <td>0.043147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(1.652, 2.447]</td>\n",
       "      <td>0.052693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(2.447, 3.243]</td>\n",
       "      <td>0.063927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(3.243, 4.038]</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ps_reg_03_dur    target\n",
       "0  (0.0573, 0.857]  0.031785\n",
       "1   (0.857, 1.652]  0.043147\n",
       "2   (1.652, 2.447]  0.052693\n",
       "3   (2.447, 3.243]  0.063927\n",
       "4   (3.243, 4.038]  0.000000"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['ps_reg_03_dur']=pd.cut(train['ps_reg_03'],5)\n",
    "train[['ps_reg_03_dur','target']].groupby(['ps_reg_03_dur'],as_index=False).mean().sort_values(by='ps_reg_03_dur',ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for data in combine:\n",
    "    data.loc[data['ps_reg_03']<=0.857,'ps_reg_03']=0\n",
    "    data.loc[(data['ps_reg_03']>0.857)&(data['ps_reg_03']<=1.652),'ps_reg_03']=1\n",
    "    data.loc[(data['ps_reg_03']>1.652)&(data['ps_reg_03']<=2.447),'ps_reg_03']=2\n",
    "    data.loc[(data['ps_reg_03']>2.447)&(data['ps_reg_03']<=3.243),'ps_reg_03']=3\n",
    "    data.loc[data['ps_reg_03']>3.243,'ps_reg_03']=4\n",
    "#     data.loc[data['ps_reg_03']>3.375,'ps_reg_03']=5\n",
    "train.drop(['ps_reg_03_dur'],axis=1,inplace=True)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理缺失列 ps_car_01_cat\t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_reg_01        0.184266\n",
       "ps_reg_02        0.231710\n",
       "ps_reg_03        0.230339\n",
       "ps_car_01_cat    1.000000\n",
       "ps_car_02_cat   -0.152733\n",
       "ps_car_03_cat    0.138055\n",
       "ps_car_05_cat   -0.147258\n",
       "ps_car_09_cat    0.272900\n",
       "ps_car_11        0.110921\n",
       "ps_car_12        0.125987\n",
       "ps_car_13        0.167127\n",
       "ps_car_15        0.102683\n",
       "Name: ps_car_01_cat, dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_01_cat'])>0.1]['ps_car_01_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#ps_reg_02和ps_car_09_cat\n",
    "combine=[train,test]\n",
    "guess_ps_car=np.zeros((10,5))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,5,1):\n",
    "            len_guess=[]\n",
    "            guess=data[(data['ps_reg_01']==i/10)&(data['ps_car_09_cat']==j)]['ps_car_01_cat'].dropna()\n",
    "            for n in range(0,12,1):\n",
    "                len_guess.append(len(guess[guess==n]))\n",
    "            guess_ps_car[i][j]=np.array(len_guess).argmax()\n",
    "    for i in range(0,10,1):\n",
    "        for j in range(0,5,1):\n",
    "            data.loc[(data['ps_reg_01']==i/10)&(data['ps_car_09_cat']==j)&(data['ps_car_01_cat'].isnull()),'ps_car_01_cat']=guess_ps_car[i][j]\n",
    "    data['ps_car_01_cat'].fillna(11,inplace=True)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充ps_car_07_cat列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_car_02_cat    0.103632\n",
       "ps_car_03_cat   -0.148339\n",
       "ps_car_04_cat   -0.183374\n",
       "ps_car_07_cat    1.000000\n",
       "ps_car_12       -0.125777\n",
       "ps_car_13       -0.223412\n",
       "Name: ps_car_07_cat, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_07_cat'])>0.1]['ps_car_07_cat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "target           False\n",
       "ps_ind_01        False\n",
       "ps_ind_02_cat    False\n",
       "ps_ind_03        False\n",
       "ps_ind_04_cat    False\n",
       "ps_ind_05_cat    False\n",
       "ps_ind_06_bin    False\n",
       "ps_ind_07_bin    False\n",
       "ps_ind_08_bin    False\n",
       "ps_ind_09_bin    False\n",
       "ps_ind_10_bin    False\n",
       "ps_ind_11_bin    False\n",
       "ps_ind_12_bin    False\n",
       "ps_ind_13_bin    False\n",
       "ps_ind_14        False\n",
       "ps_ind_15        False\n",
       "ps_ind_16_bin    False\n",
       "ps_ind_17_bin    False\n",
       "ps_ind_18_bin    False\n",
       "ps_reg_01        False\n",
       "ps_reg_02        False\n",
       "ps_reg_03        False\n",
       "ps_car_01_cat    False\n",
       "ps_car_02_cat     True\n",
       "ps_car_03_cat    False\n",
       "ps_car_04_cat    False\n",
       "ps_car_05_cat    False\n",
       "ps_car_06_cat    False\n",
       "ps_car_07_cat    False\n",
       "ps_car_08_cat    False\n",
       "ps_car_09_cat    False\n",
       "ps_car_10_cat    False\n",
       "ps_car_11_cat    False\n",
       "ps_car_11         True\n",
       "ps_car_12         True\n",
       "ps_car_13        False\n",
       "ps_car_14         True\n",
       "ps_car_15        False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "guess_pcc=np.zeros((10))\n",
    "for data in combine:\n",
    "    for i in range(0,10,1):\n",
    "        guess=data[data['ps_car_04_cat']==i]['ps_car_07_cat'].dropna()\n",
    "        if len(guess[guess==0])>len(guess[guess==1]):#找到列中出现最多次数的那个数字\n",
    "            pcc_guess=0\n",
    "        else:\n",
    "            pcc_guess=1\n",
    "        guess_pcc[i]=pcc_guess\n",
    "            \n",
    "    for i in range(0,10,1):\n",
    "            data.loc[(data['ps_car_04_cat']==i)&(data['ps_car_07_cat'].isnull()),'ps_car_07_cat']=guess_pcc[i]\n",
    "    data['ps_car_07_cat'] = data['ps_car_07_cat'].astype(int)\n",
    "combine=[train,test]\n",
    "train.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填充ps_car_14列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_car_03_cat   -0.163385\n",
       "ps_car_08_cat    0.160010\n",
       "ps_car_14        1.000000\n",
       "Name: ps_car_14, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr[abs(corr['ps_car_14'])>0.1]['ps_car_14']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guess_car14=[]\n",
    "for data in combine:\n",
    "    for i in range(0,2):\n",
    "        guess=data[data['ps_car_08_cat']==i]['ps_car_14'].dropna()\n",
    "        guess_car14.append(guess.median())\n",
    "    for i in range(0,2):\n",
    "        data.loc[(data['ps_car_14'].isnull())&(data['ps_car_08_cat']==i),'ps_car_14']=guess_car14[i]\n",
    "    data['ps_car_14'] = data['ps_car_14'].astype(float)\n",
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 删除训练集剩下很少的缺失值ps_car_02_cat和ps_car_12列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train=train.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ps_ind_01        False\n",
       "ps_ind_02_cat    False\n",
       "ps_ind_03        False\n",
       "ps_ind_04_cat    False\n",
       "ps_ind_05_cat    False\n",
       "ps_ind_06_bin    False\n",
       "ps_ind_07_bin    False\n",
       "ps_ind_08_bin    False\n",
       "ps_ind_09_bin    False\n",
       "ps_ind_10_bin    False\n",
       "ps_ind_11_bin    False\n",
       "ps_ind_12_bin    False\n",
       "ps_ind_13_bin    False\n",
       "ps_ind_14        False\n",
       "ps_ind_15        False\n",
       "ps_ind_16_bin    False\n",
       "ps_ind_17_bin    False\n",
       "ps_ind_18_bin    False\n",
       "ps_reg_01        False\n",
       "ps_reg_02        False\n",
       "ps_reg_03        False\n",
       "ps_car_01_cat    False\n",
       "ps_car_02_cat     True\n",
       "ps_car_03_cat    False\n",
       "ps_car_04_cat    False\n",
       "ps_car_05_cat    False\n",
       "ps_car_06_cat    False\n",
       "ps_car_07_cat    False\n",
       "ps_car_08_cat    False\n",
       "ps_car_09_cat    False\n",
       "ps_car_10_cat    False\n",
       "ps_car_11_cat    False\n",
       "ps_car_11         True\n",
       "ps_car_12        False\n",
       "ps_car_13        False\n",
       "ps_car_14        False\n",
       "ps_car_15        False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isnull().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充验证集缺失值很少的ps_car_02_cat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "guess=test['ps_car_02_cat'].dropna()\n",
    "len(guess[guess==0])>len(guess[guess==1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test['ps_car_02_cat'].fillna(1,inplace=True)\n",
    "test['ps_car_11'].fillna(3,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combine=[train,test]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 对连续值的列进行归类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# cols_for_dur=['ps_ind_01','ps_reg_02','ps_car_01_cat','ps_reg_01','ps_ind_15',\n",
    "#               'ps_car_11_cat','ps_car_06_cat','ps_car_12','ps_car_15','ps_ind_03']\n",
    "# # ['ps_reg_02','ps_ind_15','ps_car_11_cat','ps_car_12','ps_car_15','ps_ind_03']\n",
    "# for col in cols_for_dur:\n",
    "#     train['new_col']=pd.cut(train[col],5)\n",
    "#     list_interval=list(set(train['new_col'].values))\n",
    "#     list_interval.sort()\n",
    "#     for data in combine:\n",
    "#         data.loc[data[col]<list_interval[0].right,col]=0\n",
    "#         data.loc[(data[col]>list_interval[1].left)&(data[col]<=list_interval[1].right),col]=1\n",
    "#         data.loc[(data[col]>list_interval[2].left)&(data[col]<=list_interval[2].right),col]=2\n",
    "#         data.loc[(data[col]>list_interval[3].left)&(data[col]<=list_interval[3].right),col]=3\n",
    "#         data.loc[data[col]>list_interval[4].left,col]=4\n",
    "#     train=train.drop(['new_col'],axis=1)\n",
    "#     combine=[train,test]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# test.head(100000).to_csv(r'c:\\Users\\ZouJiu\\Desktop\\front1.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "198"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#类别形整数变量转换为类别变量\n",
    "col_cato=train.columns[train.columns.str[10]=='c']\n",
    "def int_to_category(df):\n",
    "    for col in col_cato:\n",
    "        df[col]=df[col].astype('category')\n",
    "for data in combine:\n",
    "    data=int_to_category(data)\n",
    "combine=[train,test]\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#类别形变量转换为指示性变量\n",
    "for col in col_cato:\n",
    "    new_dummies=pd.get_dummies(train[col],prefix=col)\n",
    "    train=pd.concat([train,new_dummies],axis=1)\n",
    "    train.drop([col],axis=1,inplace=True)\n",
    "for col in col_cato:\n",
    "    new_dummies=pd.get_dummies(test[col],prefix=col)\n",
    "    test=pd.concat([test,new_dummies],axis=1)\n",
    "    test.drop([col],axis=1,inplace=True)\n",
    "\n",
    "len(test.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交叉验证建模"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "K=6\n",
    "from sklearn.model_selection import StratifiedKFold\n",
    "# kf = KFold(n_splits = K,random_state = 1,shuffle = True)\n",
    "kf=StratifiedKFold(n_splits = K,random_state = 90,shuffle = True)\n",
    "X=train.drop(['target'],axis=1)\n",
    "y=train['target']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def eval_gini(y_true, y_prob):\n",
    "    y_true = np.asarray(y_true)\n",
    "    y_true = y_true[np.argsort(y_prob)]\n",
    "    ntrue = 0\n",
    "    gini = 0\n",
    "    delta = 0\n",
    "    n = len(y_true)\n",
    "    for i in range(n-1, -1, -1):\n",
    "        y_i = y_true[i]\n",
    "        ntrue += y_i\n",
    "        gini += y_i * delta\n",
    "        delta += 1 - y_i\n",
    "    gini = 1 - 2 * gini / (ntrue * (n - ntrue))\n",
    "    return gini\n",
    "def normal_gini(y_true,y_prob):\n",
    "    return eval_gini(y_true,y_prob)/eval_gini(y_true,y_true)\n",
    "def xgb_gini(pred,dtrain):\n",
    "    label=dtrain.get_label()\n",
    "    return normal_gini(label,pred)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\sklearn\\cross_validation.py:41: DeprecationWarning: This module was deprecated in version 0.18 in favor of the model_selection module into which all the refactored classes and functions are moved. Also note that the interface of the new CV iterators are different from that of this module. This module will be removed in 0.20.\n",
      "  \"This module will be removed in 0.20.\", DeprecationWarning)\n"
     ]
    }
   ],
   "source": [
    "import xgboost as xgb\n",
    "                        \n",
    "model=xgb.XGBClassifier(n_estimators=400,\n",
    "                        max_depth=5,#5\n",
    "                        objective=\"binary:logistic\",\n",
    "                        learning_rate=0.07, \n",
    "                        subsample=.8,\n",
    "                        min_child_weight=6,\n",
    "                        colsample_bytree=.8,\n",
    "                        scale_pos_weight=1.6,\n",
    "                        gamma=10,\n",
    "                        reg_alpha=8,\n",
    "                        reg_lambda=1.3)\n",
    "# model=xgb.XGBClassifier(\n",
    "#     learning_rate = 0.4,\n",
    "#  n_estimators= 4000,\n",
    "#  max_depth= 4,\n",
    "#  min_child_weight= 2,\n",
    "#  #gamma=1,\n",
    "#  gamma=0.9,                        \n",
    "#  subsample=0.8,\n",
    "#  colsample_bytree=0.8,\n",
    "#  objective= 'binary:logistic',\n",
    "#  nthread= -1,\n",
    "#  scale_pos_weight=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import lightgbm as lgb\n",
    "mode = lgb.LGBMRegressor(objective='regression',\n",
    "                        num_leaves=200,\n",
    "                        learning_rate=0.1,\n",
    "                        n_estimators=40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "len(X.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Flod: 0\n",
      "          0\n",
      "0  0.042941\n",
      "1  0.029463\n",
      "2  0.128086\n",
      "3  0.044702\n",
      "4  0.054292\n",
      "5  0.030584\n",
      "6  0.053039\n",
      "7  0.049274\n",
      "8  0.043410\n",
      "9  0.038646\n",
      "Gini: 0.286878303843\n",
      "\n",
      "Flod: 1\n",
      "          0\n",
      "0  0.026343\n",
      "1  0.034946\n",
      "2  0.033045\n",
      "3  0.027013\n",
      "4  0.024676\n",
      "5  0.052546\n",
      "6  0.031569\n",
      "7  0.051490\n",
      "8  0.045467\n",
      "9  0.047756\n",
      "Gini: 0.295890991258\n",
      "\n",
      "Flod: 2\n",
      "          0\n",
      "0  0.048152\n",
      "1  0.093901\n",
      "2  0.034687\n",
      "3  0.053097\n",
      "4  0.080001\n",
      "5  0.053997\n",
      "6  0.041007\n",
      "7  0.054646\n",
      "8  0.108539\n",
      "9  0.058567\n",
      "Gini: 0.297552728702\n",
      "\n",
      "Flod: 3\n",
      "          0\n",
      "0  0.035558\n",
      "1  0.025047\n",
      "2  0.044225\n",
      "3  0.048113\n",
      "4  0.040796\n",
      "5  0.028536\n",
      "6  0.062147\n",
      "7  0.044430\n",
      "8  0.046525\n",
      "9  0.042051\n",
      "Gini: 0.287733543471\n",
      "\n",
      "Flod: 4\n",
      "          0\n",
      "0  0.043915\n",
      "1  0.067342\n",
      "2  0.051230\n",
      "3  0.042568\n",
      "4  0.030792\n",
      "5  0.062753\n",
      "6  0.065021\n",
      "7  0.063634\n",
      "8  0.033866\n",
      "9  0.086688\n",
      "Gini: 0.280825469208\n",
      "\n",
      "Flod: 5\n",
      "          0\n",
      "0  0.038872\n",
      "1  0.055189\n",
      "2  0.051140\n",
      "3  0.081904\n",
      "4  0.024932\n",
      "5  0.065624\n",
      "6  0.061805\n",
      "7  0.027689\n",
      "8  0.024524\n",
      "9  0.036181\n",
      "Gini: 0.269898555416\n",
      "\n",
      "Flod: 6\n",
      "          0\n",
      "0  0.056443\n",
      "1  0.083086\n",
      "2  0.026918\n",
      "3  0.042564\n",
      "4  0.041392\n",
      "5  0.056827\n",
      "6  0.076442\n",
      "7  0.056669\n",
      "8  0.031041\n",
      "9  0.058554\n",
      "Gini: 0.277506861065\n",
      "\n",
      "Flod: 7\n",
      "          0\n",
      "0  0.047289\n",
      "1  0.029225\n",
      "2  0.045814\n",
      "3  0.038063\n",
      "4  0.027787\n",
      "5  0.036830\n",
      "6  0.059754\n",
      "7  0.036141\n",
      "8  0.036313\n",
      "9  0.046794\n",
      "Gini: 0.277340250169\n",
      "\n",
      "Flod: 8\n",
      "          0\n",
      "0  0.042021\n",
      "1  0.058376\n",
      "2  0.091742\n",
      "3  0.111918\n",
      "4  0.039956\n",
      "5  0.019480\n",
      "6  0.143970\n",
      "7  0.043737\n",
      "8  0.046885\n",
      "9  0.104581\n",
      "Gini: 0.297933494999\n",
      "\n",
      "Flod: 9\n",
      "          0\n",
      "0  0.077710\n",
      "1  0.097935\n",
      "2  0.070756\n",
      "3  0.044272\n",
      "4  0.062797\n",
      "5  0.077768\n",
      "6  0.041165\n",
      "7  0.074498\n",
      "8  0.039719\n",
      "9  0.043667\n",
      "Gini: 0.299112492227\n",
      "Gini for full training set: 0.287005614986\n"
     ]
    }
   ],
   "source": [
    "y_test_pred=0#0.277\n",
    "y_valid_pred=0*y\n",
    "fit_model=[]\n",
    "for i,(train_index,test_index) in enumerate(kf.split(X,y)):\n",
    "    y_train,y_valid=y.iloc[train_index],y.iloc[test_index]\n",
    "    x_train,x_valid=X.iloc[train_index],X.iloc[test_index]\n",
    "    print('\\nFlod:',i)\n",
    "    fitted_model=model.fit(x_train,y_train)\n",
    "    pred= fitted_model.predict_proba(x_valid)[:,1]\n",
    "    y_valid_pred.iloc[test_index]=pred\n",
    "    print(pd.DataFrame(pred).head(10))\n",
    "    print('Gini:',eval_gini(y_valid,pred))\n",
    "    y_test_pred+=fitted_model.predict_proba(test)[:,1]\n",
    "    fit_model.append(fitted_model)\n",
    "y_test_pred/=K\n",
    "print('Gini for full training set:',eval_gini(y,y_valid_pred))\n",
    "sub=pd.DataFrame({'id':test_id,'target':y_test_pred})\n",
    "# sub.to_csv(r'.\\KFlod_submission.csv',float_format='%.4f',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub.to_csv(r'.\\KFlod_submission.csv',float_format='%.6f',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
